﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace HPIT_Library
{
    public class DbHelper
    {
        //创建连接数据库地址对象
       private static string StrCoon = ConfigurationManager.ConnectionStrings["LibraryDB"].ConnectionString;
        //封装增删改(ExecuteNonQuery)方法
        public static int ExecuteNonQuery(string sql, params SqlParameter[] pams)
        {
            //声明一个连接对象
            using (SqlConnection coon=new SqlConnection(StrCoon))
            {
                //声明数据库操作命令对象
                using (SqlCommand cmd=new SqlCommand(sql,coon))
                {
                    //判断查询参数是否为空并且大于0
                    if (pams.Length>0&&pams!=null)
                    {
                        cmd.Parameters.AddRange(pams);
                    }
                    //判断当前对象是否为关闭状态
                    if (coon.State==ConnectionState.Closed)
                    {
                        coon.Open();
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// 封装查询首行首列(ExecuteScalar)方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pams"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] pams)
        {
            using (SqlConnection coon = new SqlConnection(StrCoon))
            {
                using (SqlCommand cmd = new SqlCommand(sql, coon))
                {
                    if (pams.Length > 0 && pams != null)
                    {
                        cmd.Parameters.AddRange(pams);
                    }
                    if (coon.State == ConnectionState.Closed)
                    {
                        coon.Open();
                    }
                    return cmd.ExecuteScalar();
                }
            }
        }
        /// <summary>
        /// 封装查询列表ExecuteReader()方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pams"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pams)
        {
            SqlConnection coon = new SqlConnection(StrCoon);
            
                using (SqlCommand cmd = new SqlCommand(sql, coon))
                {
                    if (pams.Length > 0 && pams != null)
                    {
                        cmd.Parameters.AddRange(pams);
                    }
                    if (coon.State == ConnectionState.Closed)
                    {
                        coon.Open();
                    }
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
            
        }
        public static SqlDataReader ExecuteReaderProc(string ProcName, params SqlParameter[] pams)
        {
            SqlConnection coon = new SqlConnection(StrCoon);

            using (SqlCommand cmd = new SqlCommand(ProcName, coon))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                if (pams.Length > 0 && pams != null)
                {
                    cmd.Parameters.AddRange(pams);
                }
                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }

        }
        /// <summary>
        /// 查询表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pams"></param>
        /// <returns></returns>
        public static DataTable ExecuteTable(string sql, params SqlParameter[] pams)
        {
            SqlDataAdapter sda = new SqlDataAdapter(sql,StrCoon);
            if (pams.Length > 0 && pams != null)
            {
                sda.SelectCommand.Parameters.AddRange(pams);
            }
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }
        public static DataTable ExecuteTableProc(string procName, params SqlParameter[] pams)
        {
            SqlDataAdapter sda = new SqlDataAdapter(procName, StrCoon);
            sda.SelectCommand.CommandType = CommandType.StoredProcedure;
            if (pams.Length > 0 && pams != null)
            {
                sda.SelectCommand.Parameters.AddRange(pams);
            }
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }
        public static int ExecuteNonQueryProc(string procName, params SqlParameter[] pams)
        {
            //声明一个连接对象
            using (SqlConnection coon = new SqlConnection(StrCoon))
            {
                //声明数据库操作命令对象
                using (SqlCommand cmd = new SqlCommand(procName, coon))
                {
                    //指定CommandType为存储过程
                    cmd.CommandType = CommandType.StoredProcedure;
                    //判断查询参数是否为空并且大于0
                    if (pams.Length > 0 && pams != null)
                    {
                        cmd.Parameters.AddRange(pams);
                    }
                    //判断当前对象是否为关闭状态
                    if (coon.State == ConnectionState.Closed)
                    {
                        coon.Open();
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }

    }
}